package com.buobao.jdbc.demo;

import com.buobao.jdbc.demo.entity.Employee;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DbOperation {

    /**
     * 插入数据
     * @param eno
     * @param ename
     * @param salary
     * @param department
     */
    public void insertData(int eno,String ename,float salary,String department,String hiredate) {
        Connection conn=null;
        PreparedStatement statement=null;
        try {
            conn = DbUtil.getConnection();
            String sql = "insert ignore into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
            statement = conn.prepareStatement(sql);
            statement.setInt(1,eno);
            statement.setString(2,ename);
            statement.setFloat(3,salary);
            statement.setString(4,department);
            statement.setString(5,hiredate);
            int rows = statement.executeUpdate();
            System.out.println("change "+rows);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(null,statement,conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 依据姓名查询
     * @param ename
     */
    public void searchByName(String ename) {
        Connection conn=null;
        PreparedStatement statement=null;
        ResultSet resultSet = null;
        try {
            conn = DbUtil.getConnection();
            String sql = "select * from employee where ename=?";
            statement = conn.prepareStatement(sql);
            statement.setString(1,ename);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(resultSet,statement,conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 依据no更新数据
     * @param eno
     * @param ename
     * @param salary
     * @param dname
     */
    public void update(int eno,String ename,float salary,String dname,String hiredate) {
        Connection conn=null;
        PreparedStatement statement = null;

        try {
            conn = DbUtil.getConnection();
            String sql = "update employee set ename=?,salary=?,dname=?,hiredate=? where eno=?";
            statement = conn.prepareStatement(sql);
            statement.setString(1,ename);
            statement.setFloat(2,salary);
            statement.setString(3,dname);
            statement.setInt(5,eno);
            statement.setString(4,hiredate);
            int result = statement.executeUpdate();
            if (result==0) {
                //失败
            } else {
                //成功
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(null,statement,conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 删除数据
     * @param eno
     */
    public void delete(int eno) {
        Connection conn=null;
        PreparedStatement statement = null;

        try {
            conn = DbUtil.getConnection();
            String sql = "delete ignore from employee where eno=?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1,eno);
            int result = statement.executeUpdate();
            if (result==0) {
                //失败
            } else {
                //成功
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(null,statement,conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    /**
     * 分页查询数据
     * @param page
     * @param pageSize
     * @return
     */
    public List<Employee> getListByPage(int page, int pageSize) {
        Connection conn=null;
        PreparedStatement statement=null;
        ResultSet resultSet = null;
        List<Employee> result = new ArrayList<>();
        try {
            conn = DbUtil.getConnection();
            String sql = "select * from employee limit ?,?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1,(page-1)*pageSize);
            statement.setInt(2,pageSize);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Employee employee = new Employee();
                employee.setEno(resultSet.getInt(1));
                employee.setEname(resultSet.getString(2));
                employee.setSalary(resultSet.getFloat(3));
                employee.setDname(resultSet.getString(4));
                employee.setHiredate(resultSet.getDate("hiredate"));
                result.add(employee);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtil.closeConnection(resultSet,statement,conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return result;
    }
}




















